﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;

namespace AzureConSqlserver
{
    class Program
    {
        static void Main(string[] args)
        {
            string strcon= "Server = krgg.database.chinacloudapi.cn; user=gln;pwd=KRgg208641306;database=GlnSqlserver";
            //string strcmd = "CREATE TABLE dbo.Table1" +
            //    "(" +
            //    "Id INT , " +
            //    "name NVARCHAR(32)," +
            //    "age int" +
            //    ")";
            //SqlConnection sqlcon = new SqlConnection(strcon);
            //try
            //{
            //    sqlcon.Open();
            //    SqlCommand sqlcmd = new SqlCommand(strcmd, sqlcon);
            //    sqlcmd.ExecuteNonQuery();
            //    Console.WriteLine("执行成功");
            //}
            //catch (Exception ex)
            //{
            //    Console.WriteLine(ex.Message);
            //}
            //finally
            //{
            //    sqlcon.Close();
            //}
            //Console.ReadKey();

            //增加数据
            string strInsert = "insert into GlnSqlserver.dbo.Table1(id,name,age) values(3,'gg',16)";
            OperationData(strcon, strInsert);
            Console.ReadKey();

            ////删除
            //string strDel = "delete from dbo.Table1 where id=1";
            //OperationData(strcon, strDel);
            //Console.ReadKey();

            string strupdate = "update dbo.Table1 set age=26 where id=2";
            OperationData(strcon,strupdate);
            Console.ReadKey();

            //查询
            string strSelect = "select * from dbo.Table1";
            SqlConnection sqlcon = new SqlConnection(strcon);
            sqlcon.Open();
            SqlDataAdapter sqlDA = new SqlDataAdapter(strSelect,sqlcon);
            DataSet ds = new DataSet();
            sqlDA.Fill(ds, "dbo.Table1");
            foreach (DataTable dt in ds.Tables)
            {
                Console.WriteLine(dt.TableName);
                foreach ( DataRow dr in dt.Rows)
                {
                    foreach(DataColumn dc in dt.Columns)
                    {
                        Console.Write(dr[dc]);
                        Console.Write("\t");
                    }
                    Console.WriteLine();
                }
            }
            Console.ReadKey();
        }

        /// <summary>
        /// 数据库增删改操做
        /// </summary>
        /// <param name="scon"></param>
        /// <param name="scmd"></param>
        public static void OperationData(string scon,string scmd)
        {
            SqlConnection sqlcon = new SqlConnection(scon);
            SqlCommand sqlcmd = new SqlCommand(scmd,sqlcon);
            try
            {
                sqlcon.Open();
                sqlcmd.ExecuteNonQuery();
                Console.WriteLine("执行成功");
            }
            catch (Exception ex)
            {
                Console.WriteLine("执行失败");
                Console.WriteLine(ex.Message);
            }
            finally
            {
                sqlcon.Close();
            }            
        }
    }
}
